set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
	
/******************************************************************************
**		File: 
**		Name: [dbo].a_Users_selList
**		Desc: 
**
**		Return values:
** 
**		Called by:   
**              
**		Parameters:
**		Input							Output
**     ----------							-----------
**
**		Auth: CodeSmith
**		Date: 1/8/2009 9:07:00 PM
*******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:				Description:
**		--------		--------				-------------------------------------------
**    
*******************************************************************************/
ALTER PROCEDURE [dbo].[a_Users_selList_search]
	@usertype varchar(100),
	@St bit,
	@SearchBy varchar(50),
	@SearchText nvarchar(100),
	@fDate datetime,
	@eDate datetime,
	@OrderBy varchar(50),
	@OrderDirection varchar(5),
	@Page int,
	@PageSize int,
	@TotalRecords int output
AS

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

CREATE TABLE #Tempa_Users (
	RowNumber INT IDENTITY (1, 1) NOT NULL,
	UserID int	
)

DECLARE @sql nvarchar(2000)
DECLARE @Top int

SET @Top = @Page*@PageSize
IF @PageSize > 0
   -----SET ROWCOUNT @Top
-- insert primary keys into temp table
SET @sql =	N'INSERT INTO #Tempa_Users ([UserID]) SELECT '
SET @sql = @sql + ' [UserID] FROM [dbo].[a_Users]  '
SET @sql= @sql+ ' where 1=1 '
set @sql= @sql+ ' and IsActive= '+ convert(nvarchar(10),@St)
if @usertype<>''
begin
	set @sql=@sql+' and UserTypeID in ('+@usertype+')'
end
if @SearchText<>'' and @SearchBy<>''
begin
	set @sql=@sql+ ' and '+@SearchBy+' like N'+''''+'%'+@SearchText+'%'+''''
end
if @fDate<>'1/1/1900' and @eDate='1/1/1900'
begin
	set @sql=@sql+ ' and DateOpen >='''+convert(nvarchar(50),@fDate)+''''
end
if @eDate<>'1/1/1900' and @fDate='1/1/1900'
begin
	set @sql=@sql+ ' and DateOpen <='''+convert(nvarchar(50),@eDate)+''''
end
if @fDate <>'1/1/1900' and @eDate<>'1/1/1900'
begin
	set @sql=@sql+ ' and DateOpen >='''+convert(nvarchar(50),@fDate)+''' and DateOpen <='''+convert(nvarchar(50),@eDate)+''''
end
set @sql=@sql+' ORDER BY [' + @OrderBy + N'] ' + @OrderDirection
exec (@sql)

SELECT @TotalRecords = COUNT(UserID) FROM #Tempa_Users

SELECT
	[dbo].[a_Users].[UserID],
	[CookieID],
	[Login_Email],
	[EmailConfirmed],
	[Password],
	[Firstname],
	[Lastname],
	[Title],
	[Gender],
	[Visits],
	[Hits],
	[LastVisit],
	[DateOpen],
	[UserTypeID],
	[IsActive],
	[CarID],
	[IsAccessFeatured]
FROM
	#Tempa_Users AS tblTemp JOIN [dbo].[a_Users] ON
	tblTemp.UserID = [dbo].[a_Users].UserID 	
WHERE (@PageSize = 0) OR (@PageSize > 0 AND (@Page - 1)*@PageSize < RowNumber AND RowNumber <= @Page*@PageSize)
--ORDER BY RowNumber

DROP TABLE #Tempa_Users





